package com.mbc.rmt.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import com.mbc.common.dao.DAO;
import com.mbc.common.param.CriteriaRMParams;
import com.mbc.rmt.data.CashRemittanceByAgentDataList;
import com.mbc.rmt.entities.CashReceivedByIndividualToPayAgentRpt;
import com.mbc.rmt.entities.CashRemittanceByAgent;
import com.mbc.rmt.entities.CashRemittanceByAgentRpt;

public class CashRemittanceByAgentDAOImpls extends DAO<CashRemittanceByAgent>
		implements CashRemittanceByAgentDAO {

	public CashRemittanceByAgentDAOImpls(Connection conn) {
		this.conn = conn;
		this.table_name = "CashRemittanceByAgent";
	}

	@Override
	public void create(CashRemittanceByAgent entity) throws SQLException {
		query = "insert into "
				+ table_name
				+ " ( ReceiptID, ReceiptNo, CreatedDate, ReceivedDate, DefaultBranchID, CreatedBy, FromAgentID, PaymentCurrencyID, ReceivedCurrencyID, Rate, TotalPaymentAmount, TotalReceivedAmount, ExtraFees, Received, BankID, TransactionType) values( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setLong(i++, entity.getReceiptId());
		pstmt.setString(i++, entity.getReceiptNo());
		pstmt.setTimestamp(i++, new java.sql.Timestamp(entity.getCreatedDate()
				.getTime()));
		pstmt.setTimestamp(i++, new java.sql.Timestamp(entity.getReceivedDate()
				.getTime()));
		pstmt.setLong(i++, entity.getDefaultBranchId());
		
		if(entity.getCreatedBy() != null)
			pstmt.setLong(i++, entity.getCreatedBy());
		else 
			pstmt.setString(i++, null);
		
		if(entity.getFromAgentId() != null)
			pstmt.setLong(i++, entity.getFromAgentId());
		else
			pstmt.setString(i++, null);
		
		if(entity.getPaymentCurrencyId() != null)
			pstmt.setLong(i++, entity.getPaymentCurrencyId());
		else
			pstmt.setString(i++, null);
		
		if(entity.getReceivedCurrencyId() != null)
			pstmt.setLong(i++, entity.getReceivedCurrencyId());
		else
			pstmt.setString(i++, null);
		pstmt.setDouble(i++, entity.getRate());
		pstmt.setDouble(i++, entity.getTotalPaymentAmt());
		pstmt.setDouble(i++, entity.getTotalReceivedAmt());
		pstmt.setDouble(i++, entity.getExtraFees());
		pstmt.setDouble(i++, entity.getReceived());
		
		if(entity.getBankId() != null)
			pstmt.setLong(i++, entity.getBankId());
		else 
			pstmt.setString(i++, null);
		pstmt.setInt(i++, entity.getTransactionType());
		pstmt.executeUpdate();
		pstmt.close();

	}

	@Override
	public void update(CashRemittanceByAgent entity) throws SQLException {
		query = "update "
				+ table_name
				+ " set ReceivedDate = ?, DefaultBranchID = ?, FromAgentID = ?, PaymentCurrencyID = ?, ReceivedCurrencyID = ?, Rate = ?, TotalPaymentAmount = ?, TotalReceivedAmount = ?, ExtraFees = ?, Received = ?, BankID = ?, ModifiedDate = ?, ModifiedBy = ? , TransactionType = ? where ReceiptID = "
				+ entity.getReceiptId();
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setTimestamp(i++, new java.sql.Timestamp(entity.getReceivedDate()
				.getTime()));
		pstmt.setLong(i++, entity.getDefaultBranchId());
		if(entity.getFromAgentId() != null)
			pstmt.setLong(i++, entity.getFromAgentId());
		else
			pstmt.setString(i++, null);
		if(entity.getPaymentCurrencyId() != null)
			pstmt.setLong(i++, entity.getPaymentCurrencyId());
		else
			pstmt.setString(i++, null);
		if(entity.getReceivedCurrencyId() != null)
			pstmt.setLong(i++, entity.getReceivedCurrencyId());
		else 
			pstmt.setString(i++, null);
		pstmt.setDouble(i++, entity.getRate());
		pstmt.setDouble(i++, entity.getTotalPaymentAmt());
		pstmt.setDouble(i++, entity.getTotalReceivedAmt());
		pstmt.setDouble(i++, entity.getExtraFees());
		pstmt.setDouble(i++, entity.getReceived());
		if(entity.getBankId() != null)
			pstmt.setLong(i++, entity.getBankId());
		else
			pstmt.setString(i++, null);
		pstmt.setTimestamp(i++, new java.sql.Timestamp(entity.getModifiedDate()
				.getTime()));
		if(entity.getModifiedBy() != null)
			pstmt.setLong(i++, entity.getModifiedBy());
		else
			pstmt.setString(i++, null);
		pstmt.setInt(i++, entity.getTransactionType());
		pstmt.executeUpdate();
		pstmt.close();

	}

	@Override
	public void delete(long key) throws SQLException {
		query = getDeleteQuery() + " where ReceiptID = " + key;
		pstmt = conn.prepareStatement(query);
		pstmt.executeUpdate();
		pstmt.close();

	}

	@Override
	public CashRemittanceByAgentDataList findCRByAgent(CriteriaRMParams cri)
			throws SQLException {
		CashRemittanceByAgentDataList l_DataList = new CashRemittanceByAgentDataList();
		ArrayList<CashRemittanceByAgent> l_entityList = new ArrayList<CashRemittanceByAgent>();
        int change =0;
		if ( cri.isIs_All() == true )
			change =1;
		else
			change =0;
		/*String l_cri = " and (cra.ReceiptNo like N'%" + cri.getKeyword() 
				+ "%')" + " and cra.DefaultBranchID = " + cri.getBranchId() +
				" and cra.TransactionType = " + change;*/
		String l_cri="";
		
		if(cri.getFieldname().equals("ReceiptNo"))
			l_cri = " and cra." + cri.getFieldname() + " like N'%" +cri.getKeyword() + "%'";
		else
			l_cri = " and " + cri.getFieldname() + " like N'%" + cri.getKeyword() + "%'";
		
        if( cri.isIs_All() == true ){
			query = "set rowcount "
					+ cri.getSize()
					+ " select * from(select  ROW_NUMBER()  over( order by cra.ReceiptNo desc)No, "
					+ " cra.*, a.agent_code, a.name,ba.BankCode,c.currencyrateid, c.CurrencyRateCode ,c.roper"
					+ " from CashRemittanceByAgent cra "
					+ " left join Agent a on a.agent_id= cra.FromAgentID "
					+ " left join Bank ba on ba.BankId = cra.BankID " 
					+ " left join Currency cp on cp.CurrencyId=cra.PaymentCurrencyID "
					+ " left join CurrencyRate c on c.SourceCurrencyId=cra.PaymentCurrencyID  "
					+ " and c.ConvertCurrencyId=cra.ReceivedCurrencyID "
					+ " and c.BranchID = " + cri.getBranchId()
					+ " where 1 = 1" + l_cri 
					+ " and DefaultBranchID = " + cri.getBranchId() + " and TransactionType = " + change + ") " + table_name + " where No > "+cri.getIndex() ;
        }else{
		query =   " DECLARE @homecurrencyid bigint " 
				+ " SET @homecurrencyid=(SELECT HomeCurrencyid FROM Setup)" 
				+ "set rowcount "
				+ cri.getSize()
				+ " select * from(select  ROW_NUMBER()  over( order by cra.ReceiptNo desc)No, "
				+ " cra.*, a.agent_code, a.name,ba.BankCode,c.roper, "
				+ " ISNULL(c.currencyrateid,0) currencyrateid,(CASE WHEN c.currencyratecode IS NULL AND @homecurrencyid=cra.PaymentCurrencyID" 
			    + " THEN cp.currencycode+cp.currencycode ELSE c.currencyratecode END )currencyratecode " 
				+ " from CashRemittanceByAgent cra "
				+ " left join CashRemittanceByAgentDetails crd on crd.ReceiptID=cra.ReceiptID " 
				+ " left join Agent a on a.agent_id= crd.ToAgentID "
				+ " left join Bank ba on ba.BankId = cra.BankID " 
				+ " left join Currency cp on cp.CurrencyId=cra.PaymentCurrencyID "
				+ " left join CurrencyRate c on c.SourceCurrencyId=cra.PaymentCurrencyID  "
				+ " and c.ConvertCurrencyId=@homecurrencyid "
				+ " and c.BranchID = " + cri.getBranchId()
				+ " where 1 = 1" + l_cri 
				+ " and DefaultBranchID = " + cri.getBranchId() + " and TransactionType = " + change + ") " + table_name + " where No > "+cri.getIndex() ;
        }
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		while (rs.next()) {
			l_entityList.add(readRecords(rs,"Individual"));
		}
		l_DataList.setEntityList(l_entityList);
		rs.close();
		pstmt.close();
		if( cri.isIs_All() == true )
		    query = "select count(ReceiptID) from CashRemittanceByAgent cra " 
				    + " left join Agent a on a.agent_id= cra.FromAgentID " + " where 1 = 1"
				    + l_cri + " and DefaultBranchID = " + cri.getBranchId() + " and TransactionType = " + change;
		else
			query = "select count(cra.ReceiptID) from CashRemittanceByAgent cra " 
					+ " left join CashRemittanceByAgentDetails crd on crd.ReceiptID=cra.ReceiptID " 
				    + " left join Agent a on a.agent_id= crd.ToAgentID " + " where 1 = 1"
				    + l_cri + " and DefaultBranchID = " + cri.getBranchId() + " and TransactionType = " + change;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			l_DataList.setTotalRecord(rs.getLong(1));
		}
		rs.close();
		pstmt.close();
		return l_DataList;
	}

	@Override
	protected CashRemittanceByAgent readRecord(ResultSet rs)
			throws SQLException {
		CashRemittanceByAgent l_entity = new CashRemittanceByAgent();
		l_entity.setReceiptId(rs.getLong("ReceiptID"));
		l_entity.setReceiptNo(rs.getString("ReceiptNo"));
		l_entity.setCreatedDate(rs.getTimestamp("CreatedDate"));
		l_entity.setReceivedDate(rs.getTimestamp("ReceivedDate"));
		l_entity.setDefaultBranchId(rs.getLong("DefaultBranchID"));
		l_entity.setCreatedBy(rs.getLong("CreatedBy"));
		l_entity.setToAgentCode(rs.getString("Agent_Code"));
		l_entity.setPaymentCurrencyId(rs.getLong("PaymentCurrencyID"));
		l_entity.setReceivedCurrencyId(rs.getLong("ReceivedCurrencyID"));
		l_entity.setRate(rs.getDouble("Rate"));
		l_entity.setTotalPaymentAmt(rs.getDouble("TotalPaymentAmount"));
		l_entity.setTotalReceivedAmt(rs.getDouble("TotalReceivedAmount"));
		l_entity.setExtraFees(rs.getDouble("ExtraFees"));
		l_entity.setReceived(rs.getDouble("Received"));
		l_entity.setBankId(rs.getLong("BankID"));
		/*l_entity.setModifiedDate(rs.getTimestamp("ModifiedDate"));
		l_entity.setModifiedBy(rs.getLong("ModifiedBy"));*/
		return l_entity;
	}
	
	protected CashRemittanceByAgent readRecordGL(ResultSet rs)
			throws SQLException {
		CashRemittanceByAgent l_entity = new CashRemittanceByAgent();
		l_entity.setReceiptId(rs.getLong("ReceiptID"));
		l_entity.setReceiptNo(rs.getString("ReceiptNo"));
		l_entity.setCreatedDate(rs.getTimestamp("CreatedDate"));
		l_entity.setReceivedDate(rs.getTimestamp("ReceivedDate"));
		l_entity.setDefaultBranchId(rs.getLong("DefaultBranchID"));
		l_entity.setCreatedBy(rs.getLong("CreatedBy"));
		l_entity.setFromAgentId(rs.getLong("FromAgentID"));
		l_entity.setPaymentCurrencyId(rs.getLong("PaymentCurrencyID"));
		l_entity.setReceivedCurrencyId(rs.getLong("ReceivedCurrencyID"));
		l_entity.setRate(rs.getDouble("Rate"));
		l_entity.setTotalPaymentAmt(rs.getDouble("TotalPaymentAmount"));
		l_entity.setTotalReceivedAmt(rs.getDouble("TotalReceivedAmount"));
		l_entity.setExtraFees(rs.getDouble("ExtraFees"));
		l_entity.setReceived(rs.getDouble("Received"));
		l_entity.setBankId(rs.getLong("BankID"));
		l_entity.setModifiedDate(rs.getTimestamp("ModifiedDate"));
		l_entity.setModifiedBy(rs.getLong("ModifiedBy"));
		l_entity.setTransactionType(rs.getInt("TransactionType"));
		return l_entity;
	}
	
	protected CashRemittanceByAgent readRecords(ResultSet rs,String temp)
			throws SQLException {
		CashRemittanceByAgent l_entity = new CashRemittanceByAgent();
		l_entity.setReceiptId(rs.getLong("ReceiptID"));
		l_entity.setReceiptNo(rs.getString("ReceiptNo"));
		l_entity.setCreatedDate(rs.getTimestamp("CreatedDate"));
		l_entity.setReceivedDate(rs.getTimestamp("ReceivedDate"));
		l_entity.setDefaultBranchId(rs.getLong("DefaultBranchID"));
		l_entity.setCreatedBy(rs.getLong("CreatedBy"));
		l_entity.setFromAgentId(rs.getLong("FromAgentID"));
		l_entity.setPaymentCurrencyId(rs.getLong("PaymentCurrencyID"));
		l_entity.setReceivedCurrencyId(rs.getLong("ReceivedCurrencyID"));
		l_entity.setRate(rs.getDouble("Rate"));
		l_entity.setTotalPaymentAmt(rs.getDouble("TotalPaymentAmount"));
		l_entity.setTotalReceivedAmt(rs.getDouble("TotalReceivedAmount"));
		l_entity.setExtraFees(rs.getDouble("ExtraFees"));
		l_entity.setReceived(rs.getDouble("Received"));
		l_entity.setBankId(rs.getLong("BankID"));
		l_entity.setModifiedDate(rs.getTimestamp("ModifiedDate"));
		l_entity.setModifiedBy(rs.getLong("ModifiedBy"));
		l_entity.setTransactionType(rs.getInt("TransactionType"));
		l_entity.setFromAgentCode(rs.getString("agent_code"));
		l_entity.setFromAgentName(rs.getString("name"));
		if(temp.equals("Agent")){
			l_entity.setPaymentCurrencyCode(rs.getString("PCurrency"));
			l_entity.setReceivedCurrencyCode(rs.getString("RCurrency"));
			l_entity.setBankCode(rs.getString("BankCode"));
			l_entity.setRateId(rs.getLong("CurrencyRateId"));
			l_entity.setRateCode(rs.getString("CurrencyRateCode"));
			l_entity.setRateOperator(rs.getString("Roper"));
		}
		if(temp.equals("Confirm")){
			l_entity.setPaymentCurrencyCode(rs.getString("PCurrency"));
			l_entity.setReceivedCurrencyCode(rs.getString("RCurrency"));
			l_entity.setBankCode(rs.getString("BankCode"));
		}
		if(temp.equals("Individual")){
			l_entity.setRateId(rs.getLong("CurrencyRateId"));
			l_entity.setRateCode(rs.getString("CurrencyRateCode"));
			l_entity.setBankCode(rs.getString("BankCode"));
			l_entity.setRateOperator(rs.getString("Roper"));
		}
		if(temp.equals("ToAgent")){
			l_entity.setToagentid(rs.getLong("ToAgentID"));
			l_entity.setTobranchid(rs.getLong("ToBranchID"));
			l_entity.setPaymentamt(rs.getDouble("PaymentAmount"));
			l_entity.setBalance(rs.getDouble("Balance"));
		}
		return l_entity;
	}

	@Override
	public CashRemittanceByAgent findByNo(String receiptNo,boolean status) throws SQLException {
		CashRemittanceByAgent l_entity = null;
		if( status == true ){
			query =   " DECLARE @homecurrencyid bigint " +
					  " SET @homecurrencyid=(SELECT HomeCurrencyid FROM Setup) "+
					  " select distinct cr.*, a.agent_code, a.name, cur.roper, " +
					  " ba.BankCode,isnull(cur.currencyrateid,0)currencyrateid, " +
					  " (CASE WHEN cur.currencyratecode IS NULL AND @homecurrencyid=cr.PaymentCurrencyID " +
					  " THEN c.CurrencyCode+c.CurrencyCode ELSE cur.currencyratecode END )currencyratecode " +
					  " from CashRemittanceByAgent cr "  +
					  " left join CashRemittanceByAgentDetails crd on crd.ReceiptID=cr.ReceiptID " +  
					  " left join Agent  a on a.agent_id= crd.ToAgentID " +  
					  " left join Bank ba on ba.BankId = cr.BankID " +  
					  " left join Currency c on c.CurrencyId=cr.PaymentCurrencyID " +  
					  " left join CurrencyRate cur on cur.SourceCurrencyId=cr.PaymentCurrencyID " + 
					  " and cur.ConvertCurrencyId=@homecurrencyid " +    
				      " where cr.ReceiptNo = '" + receiptNo + "'";
		}
		else{
			query = "  select cr.*, " +
					" a.agent_code, a.name,pc.CurrencyCode PCurrency,rc.CurrencyCode RCurrency,c.currencyrateid, c.CurrencyRateCode,c.roper,ba.BankCode" +
					" from CashRemittanceByAgent cr " +
					" left join CashRemittanceByAgentDetails crd on crd.ReceiptID=cr.ReceiptID " +
					" left join Agent  a on a.agent_id= cr.FromAgentID " +
					" left join Currency pc on pc.CurrencyId=cr.PaymentCurrencyID " +
					" left join Currency rc on rc.CurrencyId=cr.ReceivedCurrencyID " +
					" left join CurrencyRate c on c.SourceCurrencyId=cr.PaymentCurrencyID  " +
					" and c.ConvertCurrencyId=cr.ReceivedCurrencyID " +
					" left join Bank ba on ba.BankId = cr.BankID " +
				    " where cr.ReceiptNo = '" + receiptNo + "'";
		}
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			 if(status == true)
				 l_entity = readRecords(rs, "Individual");
			 else
			     l_entity = readRecords(rs,"Agent");
		}
		rs.close();
		pstmt.close();
		return l_entity;
	}

	@Override
	public CashRemittanceByAgent findById(long receiptId) throws SQLException {
		CashRemittanceByAgent l_crByAgent = null;
		query = "select cra.*,a.agent_code,a.name,b.BranchCode,c.CurrencyCode PCurrency," +
				" cc.CurrencyCode RCurrency,ba.BankCode from CashRemittanceByAgent cra " +
				" left join Agent a on a.agent_id = cra.FromAgentID " +
				" left join Branch b on b.BranchId = cra.DefaultBranchID " +
				" left join Currency c on c.CurrencyId = cra.PaymentCurrencyID " +
				" left join Currency cc on cc.CurrencyId = cra.ReceivedCurrencyID " +
				" left join Bank ba on ba.BankId = cra.BankID " +
				" where ReceiptID = " + receiptId;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			l_crByAgent = readRecords(rs,"Confirm");
		rs.close();
		pstmt.close();
		return l_crByAgent;
	}

	@Override
	public CashRemittanceByAgent find(long key) throws SQLException {
		CashRemittanceByAgent l_crByAgent = null;
		query = getSelectQuery() + " where receiptid = " + key;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			l_crByAgent = readRecordGL(rs);
		rs.close();
		pstmt.close();
		return l_crByAgent;
	}
	

	@Override
	public void updateStatus(Byte status, long detailkey)throws SQLException {
		query = "update "
				+ " CashRemittanceByAgentDetails_Archive "
				+ " set [status] = " + status + " where ReceiptDetailID = "
				+ detailkey ;
		pstmt = conn.prepareStatement(query);
		pstmt.executeUpdate();
	}

	@Override
	public CashRemittanceByAgent getCRByAgent(CashRemittanceByAgent entity)
			throws SQLException {
		CashRemittanceByAgent crbyAgent = null;
		query = " select cra.*,crad.ToAgentID,a.agent_code,a.name, " +
				" crad.ToBranchID,crad.PaymentAmount, " +
				" ((cra.TotalReceivedAmount+cra.ExtraFees)-cra.Received)Balance " +
				" from CashRemittanceByAgent cra " +
				" left join CashRemittanceByAgentDetails crad on cra.ReceiptID=crad.ReceiptID " +
				" left join Agent a on a.agent_id=crad.ToAgentID" +
				" left join Branch b on b.BranchId=crad.ToBranchID " +
				" where cra.ReceiptID = " + entity.getReceiptId();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			crbyAgent = readRecords(rs,"ToAgent");
		rs.close();
		pstmt.close();
		return crbyAgent;
	}

	@Override
	public ResultSet prepareCRByIndividualToPayAgentReport(
			CashReceivedByIndividualToPayAgentRpt info) throws SQLException {
		ResultSet l_rs;
		String l_query = "dbo.sp_cr_CashReceivedByIndividualToPayAgentRpt ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?";

		CallableStatement l_pstmt = conn.prepareCall(l_query);
		CashReceivedByIndividualToPayAgentRptParameter(l_pstmt, info);
		l_rs = l_pstmt.executeQuery();
		return l_rs;
	}

	private static void CashReceivedByIndividualToPayAgentRptParameter(CallableStatement l_pstmt, CashReceivedByIndividualToPayAgentRpt info) throws SQLException{
		int i = 1;
		l_pstmt.setString(i++, (new java.sql.Timestamp(info.getFromDate().getTime())).toString());
		l_pstmt.setString(i++, (new java.sql.Timestamp(info.getToDate().getTime())).toString());
		l_pstmt.setString(i++, info.getFromIssueBranchId());
		l_pstmt.setString(i++, info.getToIssueBranchId());
		l_pstmt.setString(i++, info.getFromReceiptId());
		l_pstmt.setString(i++, info.getToReceiptId());
		l_pstmt.setString(i++, info.getFromToAgentId());
		l_pstmt.setString(i++, info.getToToAgentId());
		l_pstmt.setString(i++, info.getFromCurrencyId());
		l_pstmt.setString(i++, info.getToCurrencyId());
		l_pstmt.setString(i++, info.getFromBankId());
		l_pstmt.setString(i++, info.getToBankId());
		l_pstmt.setString(i++, info.getFromUserId());
		l_pstmt.setString(i++, info.getToUserId());
		l_pstmt.setString(i++, info.getOrderby());
		l_pstmt.setLong(i++, info.getBranchId());
		
	}

	@Override
	public ResultSet prepareCRByAgentReport(CashRemittanceByAgentRpt info)
			throws SQLException {
		ResultSet l_rs;
		String l_query = "dbo.sp_cr_CashRemittanceByAgentRpt ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?";

		CallableStatement l_pstmt = conn.prepareCall(l_query);
		CashRemittanceByAgentRptParameter(l_pstmt, info);
		l_rs = l_pstmt.executeQuery();
		return l_rs;
	}
	
	private static void CashRemittanceByAgentRptParameter(CallableStatement l_pstmt, CashRemittanceByAgentRpt info) throws SQLException{
		int i = 1;
		l_pstmt.setString(i++, (new java.sql.Timestamp(info.getFromDate().getTime())).toString());
		l_pstmt.setString(i++, (new java.sql.Timestamp(info.getToDate().getTime())).toString());
		l_pstmt.setString(i++, info.getFromReceiptNo());
		l_pstmt.setString(i++, info.getToReceiptNo());
		l_pstmt.setString(i++, info.getFromFromAgentId());
		l_pstmt.setString(i++, info.getToFromAgentId());
		l_pstmt.setString(i++, info.getFromToAgentId());
		l_pstmt.setString(i++, info.getToToAgentId());
		l_pstmt.setString(i++, info.getFromToBranchId());
		l_pstmt.setString(i++, info.getToToBranchId());
		l_pstmt.setString(i++, info.getFromUserId());
		l_pstmt.setString(i++, info.getToUserId());
		l_pstmt.setString(i++, info.getFromCurrencyId());
		l_pstmt.setString(i++, info.getToCurrencyId());
		l_pstmt.setString(i++, info.getFromBankId());
		l_pstmt.setString(i++, info.getToBankId());
		l_pstmt.setString(i++, info.getStatus());
		l_pstmt.setString(i++, info.getOrderby());
		l_pstmt.setLong(i++, info.getBranchId());
		
	}

	@Override
	public CashRemittanceByAgentDataList findAllCRByAgent(CriteriaRMParams cri)
			throws SQLException {
		CashRemittanceByAgentDataList dataList = new CashRemittanceByAgentDataList();
		ArrayList<CashRemittanceByAgent> entityList = new ArrayList<CashRemittanceByAgent>();
		
//		String rcri = " and crba.DefaultBranchID = " + cri.getBranchId();
//		String ccri = " and DefaultBranchID = " + cri.getBranchId();
		
        String l_cri="";
		
		if(cri.getFieldname().equals("ReceiptNo"))
			l_cri = " and " + cri.getFieldname() + " like N'%" +cri.getKeyword() + "%'";
		else
			l_cri = " and " + cri.getFieldname() + " like N'%" + cri.getKeyword() + "%'";
		
		if ( cri.isIs_Rpt() == true && cri.isIs_All() == true)
		{
			query = " set rowcount "
					+ cri.getSize()
					+ " select * from (select  ROW_NUMBER () over (order by ReceiptNo desc)[rowno], * from (select "
					+ "crba.ReceiptID,crba.ReceiptNo,crba.CreatedDate,crba.ReceivedDate,crba.DefaultBranchID, "
					+ "(case when crba.CreatedBy is null then uu.UserId else u.UserId end)CreatedBy, "
					+ "ag.agent_code,crba.PaymentCurrencyID,crba.ReceivedCurrencyID,crba.Rate, "
					+ "crba.TotalPaymentAmount,crba.TotalReceivedAmount,crba.ExtraFees,crba.Received,crba.BankID "
					+ "from CashRemittanceByAgent crba "
					+ "left join Branch br on br.BranchId=crba.DefaultBranchID "
					+ "left join Agent ag on ag.agent_id=crba.FromAgentID "
					+ "left join Bank b on b.BankId=crba.BankID "
					+ "left join [User] u on u.UserId=crba.CreatedBy "
					+ " left join [User] uu on uu.UserId=crba.ModifiedBy "
					+ "where crba.FromAgentID IS NOT NULL and crba.DefaultBranchID = " + cri.getBranchId() + l_cri 
					+ "union  " + " select "   
					+ "crba.ReceiptID,crba.ReceiptNo,crba.CreatedDate,crba.ReceivedDate,crba.DefaultBranchID,crba.CreatedBy, "
					+ "ag.agent_code,crba.PaymentCurrencyID,crba.ReceivedCurrencyID,crba.Rate, "
					+ "crba.TotalPaymentAmount,crba.TotalReceivedAmount,crba.ExtraFees,crba.Received,crba.BankID "
					+ "from CashRemittanceByAgent_Archive crba "
					+ "left join Branch br on br.BranchId=crba.DefaultBranchID "
					+ "left join Agent ag on ag.agent_id=crba.FromAgentID "
					+ "left join Bank b on b.BankId=crba.BankID "
					+ "left join [User] u on u.UserId=crba.CreatedBy "
					+ "where crba.FromAgentID IS NOT NULL and crba.DefaultBranchID = " + cri.getBranchId() + l_cri + ") a) b where rowno > " + cri.getIndex() ;
		}
		else if(cri.isIs_Rpt() == true && cri.isIs_All() == false)
		{
			query = " set rowcount "
					+ cri.getSize()
					+ " select * from (select  ROW_NUMBER () over (order by ReceiptNo desc)[rowno], * from (select "
					+ "crba.ReceiptID,crba.ReceiptNo,crba.CreatedDate,crba.ReceivedDate,crba.DefaultBranchID," 
					+ " (case when crba.CreatedBy is null then uu.UserId else u.UserId end)CreatedBy, "
					+ "ag.agent_code,crba.PaymentCurrencyID,crba.ReceivedCurrencyID,crba.Rate, "
					+ "crba.TotalPaymentAmount,crba.TotalReceivedAmount,crba.ExtraFees,crba.Received,crba.BankID "
					+ "from CashRemittanceByAgent crba "
					+ "left join CashRemittanceByAgentDetails crbad on crbad.ReceiptID=crba.ReceiptID "
					+ "left join Branch br on br.BranchId=crba.DefaultBranchID "
					+ "left join Agent ag on ag.agent_id=crbad.ToAgentID "
					+ "left join Bank b on b.BankId=crba.BankID "
					+ "left join [User] u on u.UserId=crba.CreatedBy "
					+ "left join [User] uu on uu.UserId=crba.ModifiedBy "
					+ "where crba.FromAgentID IS NULL " + l_cri 
					+ "union " + "select "   
					+ "crba.ReceiptID,crba.ReceiptNo,crba.CreatedDate,crba.ReceivedDate,crba.DefaultBranchID,crba.CreatedBy, "
					+ "ag.agent_code,crba.PaymentCurrencyID,crba.ReceivedCurrencyID,crba.Rate, "
					+ "crba.TotalPaymentAmount,crba.TotalReceivedAmount,crba.ExtraFees,crba.Received,crba.BankID "
					+ "from CashRemittanceByAgent_Archive crba "
					+ "left join CashRemittanceByAgentDetails_Archive crbad on crbad.ReceiptID=crba.ReceiptID "
					+ "left join Branch br on br.BranchId=crba.DefaultBranchID "
					+ "left join Agent ag on ag.agent_id=crbad.ToAgentID "
					+ "left join Bank b on b.BankId=crba.BankID "
					+ "left join [User] u on u.UserId=crba.CreatedBy "
					+ "where crba.FromAgentID IS NULL " + l_cri + ") a) b where rowno > " + cri.getIndex() + " and DefaultBranchID = " + cri.getBranchId();
		}
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		while (rs.next()) {
			entityList.add(readRecord(rs));
		}
		dataList.setEntityList(entityList);
		rs.close();
		pstmt.close();
		if ( cri.isIs_Rpt() == true && cri.isIs_All() == true){
		    query = "select ( " +
				    "(select count(*) from CashRemittanceByAgent crba left join Agent ag on ag.agent_id=crba.FromAgentID where FromAgentID IS NOT NULL "  + l_cri + " and DefaultBranchID = " + cri.getBranchId() + ") "
				    + " + (select count(*) from CashRemittanceByAgent_Archive crba left join Agent ag on ag.agent_id=crba.FromAgentID where FromAgentID IS NOT NULL " + l_cri + " and DefaultBranchID = " + cri.getBranchId() + ") ) ";
		}
		else if(cri.isIs_Rpt() == true && cri.isIs_All() == false){
			query = "select ( " +
				    "(select count(*) from CashRemittanceByAgent crba " +
					"left join CashRemittanceByAgentDetails crbad on crbad.ReceiptID=crba.ReceiptID "+
				    "left join Agent ag on ag.agent_id=crbad.ToAgentID where FromAgentID IS NULL "  + l_cri + " and DefaultBranchID = " + cri.getBranchId() + ") "
				    + " + (select count(*) from CashRemittanceByAgent_Archive crba " +
					"left join CashRemittanceByAgentDetails crbad on crbad.ReceiptID=crba.ReceiptID " +
					"left join Agent ag on ag.agent_id=crbad.ToAgentID where FromAgentID IS NULL " + l_cri + " and DefaultBranchID = " + cri.getBranchId() + ") ) ";
		}
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			dataList.setTotalRecord(rs.getLong(1));
		}
		rs.close();
		pstmt.close();
		return dataList;
	}
	
}
